﻿using System;
using System.Web;
using mimosa_web.Storage;
using System.Xml;
using Npgsql;
using System.Net.Json;
using mimosa_web;

namespace mimosa_web.Biz
{
    public class UserMng 
    {
        public static bool GetData(string opType, param[] parameterArry, ref string outData)
        {
            bool bResult = true;
            NpgsqlConnection connection = DBConn.GetConnection();
            if (connection == null)
                return false;

            string xPath = string.Format("/script/sql/{0}",opType);
            using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
            {
                NpgsqlDataReader reader = null;
                try
                {
                    int rowCount = 0;
                    JsonObjectCollection res = new JsonObjectCollection();
                    JsonArrayCollection recordset = new JsonArrayCollection("recordset");
                    if (parameterArry != null)
                    {
                        for (int i = 0; i < parameterArry.Length; i++)
                            cmd.Parameters.Add(new NpgsqlParameter("acct_id", parameterArry[i].getValue()));
                    }

                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (rowCount == 0)
                        {
                            JsonArrayCollection headerItem = new JsonArrayCollection("column_header");
                            res.Add( headerItem);
                            for (int i = 0; i < reader.FieldCount; i++)
                                headerItem.Add(new JsonStringValue(null, reader.GetName(i)));
                        }

                        JsonArrayCollection items = new JsonArrayCollection();
                        recordset.Add(items);
                        for (int i = 0; i < reader.FieldCount; i++)
                            items.Add(new JsonStringValue(null, reader[i].ToString()));
                        
                        rowCount++;
                    }
                    res.Add(recordset);
                    res.Add(new JsonStringValue("row_count", rowCount.ToString()));
                    outData = res.ToString();
                }
                catch (Exception e)
                {
                    outData = e.StackTrace;
                    bResult = false;
                }
                finally
                {
                    // Always call Close when done reading.
                    if (reader != null)
                        reader.Close();

                    if(connection.State == System.Data.ConnectionState.Open)
                        connection.Close();
                }
            }

            return bResult;
        }

        public static bool CreateAccount(string jData, param[] parameterArry, ref string outData)
        {
            bool bResult = true;
            NpgsqlConnection connection = DBConn.GetConnection();
            if (connection == null)
                return false;

            string xPath = string.Format("/script/sql/{0}", "insert_account");
            
            try
            {
                JsonTextParser parser = new JsonTextParser();
                JsonObjectCollection jObject = parser.Parse(jData) as JsonObjectCollection; 

                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("acct_id",jObject["acct_id"].GetValue().ToString()));
                    cmd.Parameters.Add(new NpgsqlParameter("acct_nm",jObject["acct_nm"].GetValue().ToString()));
                    cmd.Parameters.Add(new NpgsqlParameter("pwd", jObject["pwd"].GetValue().ToString()));
                    cmd.Parameters.Add(new NpgsqlParameter("acct_type", jObject["acct_type"].GetValue().ToString()));

                    int ret = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                xPath = string.Format("/script/sql/{0}", "insert_contract");
                JsonArrayCollection jac = jObject["contract"] as JsonArrayCollection;
                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    foreach (JsonObjectCollection joc in jac)
                    {
                        cmd.Parameters.Add(new NpgsqlParameter("acct_id", jObject["acct_id"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("svc_cd", joc["svc_cd"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("aval_st_dt", joc["aval_st_dt"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("aval_end_dt", joc["aval_end_dt"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("phone_id", joc["phone_id"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("vendor", joc["vendor"].GetValue().ToString()));

                        int ret = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                //connection.BeginTransaction(System.Data.IsolationLevel.
            }
            catch (Exception e)
            {
                outData = e.StackTrace;
                bResult = false;
            }
            finally
            {
                // Always call Close when done reading.
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
            }
            

            return bResult;
        }

        public static bool DeleteAccount(string accountId, param[] parameterArry, ref string outData)
        {
            bool bResult = true;
            NpgsqlConnection connection = DBConn.GetConnection();
            if (connection == null)
                return false;

            string xPath = string.Format("/script/sql/{0}", "delete_account");

            try
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("acct_id", accountId));
                    int ret = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                xPath = string.Format("/script/sql/{0}", "delete_contract");
                
                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {

                    cmd.Parameters.Add(new NpgsqlParameter("acct_id", accountId));
                    int ret = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    
                }
                //connection.BeginTransaction(System.Data.IsolationLevel.
            }
            catch (Exception e)
            {
                outData = e.StackTrace;
                bResult = false;
            }
            finally
            {
                // Always call Close when done reading.
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
            }


            return bResult;
        }

        public static bool UpdateSysContract(string jData, param[] parameterArry, ref string outData)
        {
            bool bResult = true;
            NpgsqlConnection connection = DBConn.GetConnection();
            if (connection == null)
                return false;

            string xPath = string.Format("/script/sql/{0}", "delete_contract");

            try
            {
                JsonTextParser parser = new JsonTextParser();
                JsonObjectCollection jObject = parser.Parse(jData) as JsonObjectCollection;

                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("acct_id", jObject["acct_id"].GetValue().ToString()));
                    int ret = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

                System.Collections.Generic.List<string> macAddrList = new System.Collections.Generic.List<string>();
                xPath = string.Format("/script/sql/{0}", "insert_sys_contract");
                JsonArrayCollection jac = jObject["mac"] as JsonArrayCollection;
                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    foreach (JsonStringValue joc in jac)
                    {
                        cmd.Parameters.Add(new NpgsqlParameter("acct_id", jObject["acct_id"].GetValue().ToString()));
                        cmd.Parameters.Add(new NpgsqlParameter("mac_addr", joc.GetValue().ToString()));

                        int ret = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        if (0 == ret)
                            macAddrList.Add(joc.GetValue().ToString());
                    }
                }

                if (macAddrList.Count > 0)
                {
                    xPath = string.Format("/script/sql/{0}", "insert_new_phone");
                    using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                    {
                        foreach (string macAddr in macAddrList)
                        {
                            cmd.Parameters.Add(new NpgsqlParameter("mac_addr", macAddr));

                            int ret = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                }

                xPath = string.Format("/script/sql/{0}", "get_system_contract");
                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    NpgsqlDataReader reader = null;
                    cmd.Parameters.Add(new NpgsqlParameter("acct_id", jObject["acct_id"].GetValue().ToString()));

                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        outData += reader[0].ToString() + ",";
                    }

                    if(outData.Length > 0)
                        outData = outData.Remove(outData.Length - 1, 1);
                    
                    reader.Close();
                }
                //connection.BeginTransaction(System.Data.IsolationLevel.
            }
            catch (Exception e)
            {
                outData = e.StackTrace;
                bResult = false;
            }
            finally
            {
                // Always call Close when done reading.
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
            }

            return bResult;
        }

        public static bool SetCallTime(string jData, param[] parameterArry, ref string outData)
        {
            bool bResult = true;
            NpgsqlConnection connection = DBConn.GetConnection();
            if (connection == null)
                return false;

            string xPath = string.Format("/script/sql/{0}", "update_calltime");

            try
            {
                JsonTextParser parser = new JsonTextParser();
                JsonObjectCollection jObject = parser.Parse(jData) as JsonObjectCollection;

                using (NpgsqlCommand cmd = new NpgsqlCommand(DBManager.GetScript(xPath), connection))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("hangup_type", jObject["hangup_type"].GetValue().ToString()));
                    cmd.Parameters.Add(new NpgsqlParameter("call_msec", jObject["call_msec"].GetValue().ToString()));
                    cmd.Parameters.Add(new NpgsqlParameter("from_user", jObject["from_user"].GetValue().ToString()));
                    int ret = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            catch (Exception e)
            {
                outData = e.StackTrace;
                bResult = false;
            }
            finally
            {
                // Always call Close when done reading.
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
            }

            return bResult;
        }
    }
}